------------------------------------------------------------------------------------------------------------------
USE ReportesGerenciales;
GO

----------------------------------------------------------------------------------------------------------------
DROP PROCEDURE InsertarUsuario;
GO

CREATE PROCEDURE InsertarUsuario(@user varchar(20),@pass varchar(40),@tipo numeric(2,0))
AS
BEGIN
	BEGIN TRANSACTION
	INSERT INTO Usuario(usuario,pass,id_tipo_usuario) VALUES(@user,@pass,@tipo);
	IF @@ERROR != 0
		BEGIN
			ROLLBACK TRANSACTION
			RETURN
		END
	COMMIT TRANSACTION
END;
GO

----------------------------------------------------------------------------------------------------------------
DROP PROCEDURE ActualizarUsuario;
GO

CREATE PROCEDURE ActualizarUsuario(@user varchar(20),@pass varchar(40),@tipo numeric(2,0),@id numeric(9,0))
AS
BEGIN
	BEGIN TRANSACTION
	UPDATE Usuario SET usuario=@user,pass=@pass,id_tipo_usuario=@tipo WHERE id_usuario=@id;
	IF @@ERROR != 0
		BEGIN
			ROLLBACK TRANSACTION
			RETURN
		END
	COMMIT TRANSACTION
END;
GO

----------------------------------------------------------------------------------------------------------------
DROP PROCEDURE EliminarUsuario;
GO

CREATE PROCEDURE EliminarUsuario(@id numeric(9,0))
AS
BEGIN
	BEGIN TRANSACTION
	DELETE FROM Usuario WHERE id_usuario=@id;
	IF @@ERROR != 0
		BEGIN
			ROLLBACK TRANSACTION
			RETURN
		END
	COMMIT TRANSACTION
END;
GO

----------------------------------------------------------------------------------------------------------------
DROP PROCEDURE ListarUsuarios;
GO

CREATE PROCEDURE ListarUsuarios (@inicio as integer, @total as integer)
AS
BEGIN
	DECLARE @sql varchar(255)

	SET @sql =
	'SELECT TOP ('+CONVERT(varchar, @total)+') '+
		'u.id_usuario AS id, u.usuario AS usr, tu.descripcion AS tipo_usu, tu.id_tipo AS id_tip '+
		'FROM Usuario u '+
		'INNER JOIN TipoUsuario tu ON u.id_tipo_usuario=tu.id_tipo '+
	'WHERE u.id_usuario NOT IN (SELECT TOP ('+CONVERT(varchar, @inicio)+') id_usuario FROM Usuario)';

	EXEC (@sql);
END;
GO

----------------------------------------------------------------------------------------------------------------
DROP PROCEDURE ContarUsuarios;
GO

CREATE PROCEDURE ContarUsuarios
AS
BEGIN
	SELECT COUNT(*) as num FROM Usuario;
END;
GO

----------------------------------------------------------------------------------------------------------------
DROP PROCEDURE ValidarLogin;
GO

CREATE PROCEDURE ValidarLogin (@user as varchar(20), @pwd as varchar(40))
AS
BEGIN
	DECLARE @id numeric(9,0);
	SET @id=(SELECT TOP 1 id_usuario FROM Usuario WHERE usuario=@user AND pass=@pwd);
	IF @id IS NULL SET @id=-1;
	SELECT @id as id;
END;
GO

----------------------------------------------------------------------------------------------------------------
DROP PROCEDURE ObtenerUsuario;
GO

CREATE PROCEDURE ObtenerUsuario (@id numeric(9,0))
AS
BEGIN
	SELECT u.id_usuario AS id, u.usuario AS usr, u.pass AS pass, tu.id_tipo AS id_tip
	FROM Usuario u
	INNER JOIN TipoUsuario tu ON u.id_tipo_usuario=tu.id_tipo
	WHERE u.id_usuario=@id
END;
GO

----------------------------------------------------------------------------------------------------------------
DROP PROCEDURE ObtenerUsuarioPorNombre;
GO

CREATE PROCEDURE ObtenerUsuarioPorNombre (@user as varchar(20))
AS
BEGIN
	SELECT u.id_usuario AS id, u.usuario AS usr, tu.descripcion AS tipo_usu, tu.id_tipo AS id_tip
	FROM Usuario u
	INNER JOIN TipoUsuario tu ON u.id_tipo_usuario=tu.id_tipo
	WHERE u.usuario=@user
END;
GO

----------------------------------------------------------------------------------------------------------------
-- REHABILITAR

-- GRANT EXECUTE ON InsertarUsuario TO master
-- GRANT EXECUTE ON ActualizarUsuario TO master
-- GRANT EXECUTE ON EliminarUsuario TO master
-- GRANT EXECUTE ON ContarUsuarios TO master
-- GRANT EXECUTE ON ValidarLogin TO master
-- GRANT EXECUTE ON ObtenerUsuario TO master
-- GRANT EXECUTE ON ObtenerUsuarioPorNombre TO master
-- GRANT EXECUTE ON ListarUsuarios TO master

-- REVOKE EXECUTE ON InsertarUsuario TO usuario
-- REVOKE EXECUTE ON ActualizarUsuario TO usuario
-- REVOKE EXECUTE ON EliminarUsuario TO usuario
-- REVOKE EXECUTE ON ContarUsuarios TO usuario
-- GRANT EXECUTE ON ValidarLogin TO usuario
-- GRANT EXECUTE ON ObtenerUsuario TO usuario
-- GRANT EXECUTE ON ObtenerUsuarioPorNombre TO usuario
-- REVOKE EXECUTE ON ListarUsuarios TO usuario
